﻿using SIR.Common;
using SIR.Common.Condition;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace SIR.DAL
{
	public class DataServer
	{
		/// <summary>
		/// Establishes a connection using the specified connection string
		/// </summary>
		/// <param name="connectionString">the connection string for the SQL connection</param>
		/// <returns>True if a connection could be made</returns>
		public static bool Connect(string connectionString)
		{
			var errorMessage = string.Empty;
			var isConnected = Connect(connectionString, out errorMessage);
			if (!string.IsNullOrWhiteSpace(errorMessage))
				throw new Exception(errorMessage);
			return true;
		}

		public static void Disconnect()
		{
			if (!IsConnected())
				return;
			// Should be a better way to close the connection
			//_server.Dispose();
			_server = null;
		}

		/// <summary>
		/// Establishes a connection using the specified connection string
		/// </summary>
		/// <param name="connectionString">The connection string for the SQL connection</param>
		/// <param name="errorMessage">The error message returned while making the connection</param>
		/// <returns>True if a connection could be made</returns>
		public static bool Connect(string connectionString, out string errorMessage)
		{
			// Fail if the connection string is empty
			if (string.IsNullOrWhiteSpace(connectionString))
			{
				errorMessage = "Connection string is required";
				return false;
			}

			// Fail if already connected
			if (IsConnected())
			{
				errorMessage = "A connection to the database has already been established";
				return true;
			}

			// Fail if required connection string components are not there
			if (!connectionString.Contains("Data Source=") || !connectionString.Contains("Initial Catalog="))
			{
				errorMessage = "Invalid connection string";
				return false;
			}

			errorMessage = null;
			try
			{
				// try to create a data server instance with the connection string
				_server = new DataServer(connectionString);
			}
			catch (Exception)
			{
				// Catch the execption and return it's message as a failure
				_server = null;
				errorMessage = "Could not connect to the server";
				return false;
			}

			// Connection established
			return true;
		}

		/// <summary>
		/// Create a DataServer instance with the specified connection string
		/// </summary>
		/// <param name="connectionString">the Sql Connection string</param>
		private DataServer(string connectionString)
		{
			try
			{
				// Try to establish and open the connection
				_connection = new SqlConnection(connectionString);
				_connection.Open();
				_isConnected = true;
			}
			catch (Exception)
			{
				// Reset and re-throw the exception
				_connection = null;
				_isConnected = false;
				throw;
			}
		}

		private bool _isConnected;
		private SqlConnection _connection;
		private static DataServer _server;

		/// <summary>
		/// Returns true if the server has been created and connection established
		/// </summary>
		/// <returns>True if there is an active connection</returns>
		public static bool IsConnected()
		{
			return _server != null && _server._isConnected;
		}

		/// <summary>
		/// Returns a DataServerResults object with a list of items, of the specified type, based on the condition
		/// </summary>
		/// <typeparam name="T">The type of object to retrieve</typeparam>
		/// <param name="condition">Optional condition to limit the results set</param>
		/// <returns>A DataServerResults object containing the list of items or the error message</returns>
		public static DataServerResults<T> Find<T>(IPredicate condition = null) where T : IEntity
		{
			// Call the generic find method
			var results = Find(typeof(T), condition);
			// Format the Results for the specified type
			return new DataServerResults<T>(condition)
			{
				WasSuccessful = results.WasSuccessful,
				ErrorMessage = results.ErrorMessage,
				Results = results.Results.Count() > 0 ? results.Results.Cast<T>().ToList() : new List<T>()
			};
		}

		/// <summary>
		/// Returns a DataServerResults object with a list of items, of IEntity, based on the condition
		/// </summary>
		/// <typeparam name="T">The type of object to retrieve</typeparam>
		/// <param name="condition">Optional condition to limit the results set</param>
		/// <returns>A DataServerResults object containing the list of items or the error message</returns>
		public static DataServerResults<IEntity> Find(Type T, IPredicate condition = null)
		{
			// Use the find by join to make it easier, but with no joining table
			return FindByJoin(T, null, condition);
		}

		/// <summary>
		/// Returns a DataServerResults object with a list of items, of IEntity, based on the condition and joining table
		/// </summary>
		/// <param name="T">The type of object to retrieve</param>
		/// <param name="J">The type of object to join to the retrieval object</param>
		/// <param name="condition">Condition to limit the results set</param>
		/// <returns>A DataServerResults object containing the list of items or the error message</returns>
		public static DataServerResults<IEntity> FindByJoin(Type T, Type J, IPredicate condition)
		{
			// Build the query and add the joining table if specified
			var query = string.Format("SELECT * FROM [dbo].[{0}]{1}", T.Name,
				J == null ? string.Empty : string.Format(" INNER JOIN [dbo].[{1}] ON [{0}].[Id] = [{1}].[{0}Id]", T.Name, J.Name));
			// Retrieve the data
			return ExecuteReader(T, query, condition);
		}

		/// <summary>
		/// Returns a DataServerResults object with a list of items, of IEntity, based on the query and condition
		/// </summary>
		/// <param name="T">The type of object to retrieve</param>
		/// <param name="query">The SQL query to be executed against the SQL connection</param>
		/// <param name="condition">Condition to limit the results set</param>
		/// <returns>A DataServerResults object containing the list of items or the error message</returns>
		private static DataServerResults<IEntity> ExecuteReader(Type T, string query, IPredicate condition)
		{
			// Initialize the results
			var results = new DataServerResults<IEntity>(condition);
			// If not connected, set as unsuccessful
			if (!IsConnected())
			{
				results.WasSuccessful = false;
				results.ErrorMessage = "No connection has been established";
			}
			else
			{
				try
				{
					// Get the properties on the type of return object to be populated from the SELECT statement
					var properties = GetReadProperties(T);

					// Add the "WHERE" clause on the select statement if needed
					if (condition != null && query.Contains("SELECT"))
						query += string.Format(" WHERE {0}", condition.Format());

					using (var command = new SqlCommand(query, _server._connection))
					{
						// Populate any parameters on the command
						if (condition != null)
						{
							var parameters = condition.GetParameters();
							foreach (var kvp in parameters)
							{
								command.Parameters.AddWithValue(kvp.Key, kvp.Value);
							}
						}

						// Execute the SELECT statement
						using (var dataReader = command.ExecuteReader())
						{
							// Read each result record
							while (dataReader.Read())
							{
								try
								{
									// Create a new instance of the object
									var newItem = Activator.CreateInstance(T) as IEntity;
									// Reading from the DB, object is not new
									newItem.IsNew = false;
									// Loop through all of the properties
									foreach (var property in properties)
									{
										try
										{
											// Try to retrieve the properties from the SELECT results and set it on the object
											var index = dataReader.GetOrdinal(property.Name);
											property.SetValue(newItem, Convert.ChangeType(dataReader.GetValue(index), property.PropertyType));
										}
										catch (IndexOutOfRangeException)
										{
											// ignore and move to next property
										}
									}

									// Add the item to the results list
									results.Results.Add(newItem);
								}
								catch (Exception subEx)
								{
									// Append the exception message to the error message
									results.ErrorMessage += string.Format(" An error has occured: {0}; ", subEx.Message);
								}
							}
						}
					}

					// Find was successful
					results.WasSuccessful = true;
				}
				catch (Exception ex)
				{
					// An exception occured, log it and set the results to unsuccessful
					LogException(ex.Message);
					results.WasSuccessful = false;
					results.ErrorMessage = "Condition was invalid";
				}
			}

			// Return the results
			return results;
		}

		/// <summary>
		/// Saves the entity to the database and returns a result set containing the previous and new version of the object
		/// </summary>
		/// <typeparam name="T">The type of the object to saved and then returned</typeparam>
		/// <param name="entity">The entity to be saved</param>
		/// <returns>A DataServerResult object containing the saved object an error message if necessary</returns>
		public static DataServerResult<T> Save<T>(T entity)
			where T : class, IEntity
		{
			// Get the retusl from the generic save method
			var result = Save(typeof(T), entity);
			// Convert the results to the specific type specified
			return new DataServerResult<T>()
			{
				WasSuccessful = result.WasSuccessful,
				ErrorMessage = result.ErrorMessage,
				NewValue = result.NewValue as T,
				OldValue = result.OldValue as T
			};
		}

		/// <summary>
		/// Saves the entity to the database and returns a result set containing the previous and new version of the object
		/// </summary>
		/// <param name="T">The type of the object to saved and then returned</param>
		/// <param name="entity">The entity to be saved</param>
		/// <returns>A DataServerResult object containing the saved object an error message if necessary</returns>
		public static DataServerResult<IEntity> Save(Type T, IEntity entity)
		{
			// Check if connected
			if (!IsConnected())
			{
				// Not connected, return an unsuccessful result
				return new DataServerResult<IEntity>()
				{
					ErrorMessage = "No connection has been established",
					OldValue = entity,
					NewValue = null,
					WasSuccessful = false
				};
			}

			// If the entity is null
			if (entity == null)
			{
				// Return unsuccessful result
				return new DataServerResult<IEntity>()
				{
					ErrorMessage = "Invalid save call, something must be provided to save",
					OldValue = entity,
					NewValue = null,
					WasSuccessful = false
				};
			}

			// If new, insert
			if (entity.IsNew)
				return _server.Insert(T, entity);
			// If deleted, then delete
			if (entity.IsDeleted)
				return _server.Delete(T, entity);
			// otherwise, update
			return _server.Update(T, entity);
		}

		private static string _insertQueryFormat = "DECLARE @resultTbl TABLE ([Id] BIGINT); " +
						"INSERT INTO [dbo].[{0}] ({1}) OUTPUT INSERTED.[Id] INTO @resultTbl VALUES ({2}); " +
						"SELECT TOP 1 @Id = [Id] FROM @resultTbl;";
		/// <summary>
		/// Inserts the passed object into the database
		/// </summary>
		/// <param name="T">The type of the object being inserted</param>
		/// <param name="entity">The object to be inserted</param>
		/// <returns>The results object containing the new object after insert</returns>
		private DataServerResult<IEntity> Insert(Type T, IEntity entity)
		{
			try
			{
				// Get the properties that can be written
				var properties = GetWriteProperties(T);

				// Format the query with the name of the object type and the properties on the object
				var query = string.Format(_insertQueryFormat, T.Name,
					string.Join(", ", properties.Select(p => { return "[" + p.Name + "]"; })),
					string.Join(", ", properties.Select(p => { return "@" + p.Name; })));

				// Initialize the command
				using (var command = new SqlCommand(query, _connection))
				{
					// Add the properties of the object as parameter values
					foreach (var property in properties)
					{
						command.Parameters.AddWithValue(property.Name, property.GetValue(entity) ?? "");
					}

					// Add the "ID" as an output parameter
					var idParam = command.Parameters.AddWithValue("Id", 0);
					idParam.Direction = System.Data.ParameterDirection.Output;

					// Execute the INSERT
					var resultCount = command.ExecuteNonQuery();

					// Return the results as a "SELECT" executed against the DB
					return new DataServerResult<IEntity>()
					{
						ErrorMessage = string.Empty,
						// Return the new value as the value of the record in the DB, not the value passed
						NewValue = Find(T, new Predicate("Id", idParam.Value, PredicateOperator.EqualTo)).Results.FirstOrDefault(),
						OldValue = null,
						WasSuccessful = resultCount > 0
					};
				}
			}
			catch (Exception ex)
			{
				// Log the exception and send the failed result back
				LogException(ex.Message);
				return new DataServerResult<IEntity>()
				{
					ErrorMessage = string.Format("An error occured while trying to insert {0}. {1}", entity.ToString(), ex.Message),
					NewValue = null,
					OldValue = null,
					WasSuccessful = false
				};
			}
		}

		/// <summary>
		/// Updates the passed object into the database
		/// </summary>
		/// <param name="T">The type of the object being updated</param>
		/// <param name="entity">The object to be updated</param>
		/// <returns>The results object containing the new object after the update</returns>
		private DataServerResult<IEntity> Update(Type T, IEntity entity)
		{
			try
			{
				// Gets the writable properties for the type of object
				var properties = GetWriteProperties(T);

				// Build the list of fields and parameters for the query
				var updateFields = new List<string>();
				foreach (var property in properties)
				{
					updateFields.Add(string.Format("[{0}] = @{0}", property.Name));
				}

				// Build the SQL query
				var query = string.Format("UPDATE [dbo].[{0}] SET {1} WHERE [Id] = @Id", T.Name,
					string.Join(", ", updateFields));

				// Initalize the command
				using (var command = new SqlCommand(query, _connection))
				{
					// Add the properies as command parameters
					foreach (var property in properties)
					{
						command.Parameters.AddWithValue(property.Name, property.GetValue(entity) ?? "");
					}

					// Add a parameter for the identifier
					command.Parameters.AddWithValue("Id", entity.Id);

					// Execute the update
					var resultCount = command.ExecuteNonQuery();

					// Throw an exception if multiple records have been updated
					if (resultCount > 1)
						throw new Exception("Should not be updating more than one record per entity");

					// Return the successful results with the new value being read back from the database
					return new DataServerResult<IEntity>()
					{
						ErrorMessage = string.Empty,
						NewValue = Find(T, new Predicate("Id", entity.Id, PredicateOperator.EqualTo)).Results.FirstOrDefault(),
						OldValue = entity,
						WasSuccessful = resultCount == 1
					};
				}
			}
			catch (Exception ex)
			{
				// Log the exception and send the failed result back
				LogException(ex.Message);
				return new DataServerResult<IEntity>()
				{
					ErrorMessage = string.Format("An error occured while trying to update {0}. {1}", entity.ToString(), ex.Message),
					NewValue = entity,
					OldValue = entity,
					WasSuccessful = false
				};
			}
		}

		/// <summary>
		/// Deleted the passed object from the database
		/// </summary>
		/// <param name="T">The type of the object being deleted</param>
		/// <param name="entity">The object to be deleted</param>
		/// <returns>The results object containing the object after the delete as the old value, new value is null</returns>
		private DataServerResult<IEntity> Delete(Type T, IEntity entity)
		{
			try
			{
				// Format the delete statement
				var query = string.Format("DELETE FROM [dbo].[{0}] WHERE [Id] = @Id", T.Name);
				using (var command = new SqlCommand(query, _connection))
				{
					// Add a parameter with the identifier for the object
					command.Parameters.AddWithValue("Id", entity.Id);
					// Execute the delete
					var resultCount = command.ExecuteNonQuery();

					// Throw and exception if more than one record was deleted
					if (resultCount > 1)
						throw new Exception("Too many records were deleted");

					// Return the results, if result count was 1 then success, otherwise failure
					return new DataServerResult<IEntity>()
					{
						ErrorMessage = string.Empty,
						NewValue = resultCount == 1 ? null : entity,
						OldValue = entity,
						WasSuccessful = resultCount == 1
					};
				}
			}
			catch (Exception ex)
			{
				// Log the exception and send the failed result back
				LogException(ex.Message);
				return new DataServerResult<IEntity>()
				{
					ErrorMessage = string.Format("An error occured while trying to delete {0}. {1}", entity.ToString(), ex.Message),
					NewValue = entity,
					OldValue = entity,
					WasSuccessful = false
				};
			}
		}

		/// <summary>
		/// Returns a list of the properties that can be read for the passed type
		/// </summary>
		/// <param name="T">The type of object to get properties for</param>
		/// <returns>A list of properties on the type for reading</returns>
		private static IEnumerable<PropertyInfo> GetReadProperties(Type T)
		{
			return GetProperties(T, DatabaseAccessRights.Read);
		}

		/// <summary>
		/// Returns a list of the properties that can be written for the passed type
		/// </summary>
		/// <param name="T">The type of object to get properties for</param>
		/// <returns>A list of properties on the type for writting values</returns>
		private static IEnumerable<PropertyInfo> GetWriteProperties(Type T)
		{
			return GetProperties(T, DatabaseAccessRights.Write);
		}

		/// <summary>
		/// Returns a list of the properties that meet the rights
		/// </summary>
		/// <param name="T">The type of object to get properties for</param>
		/// <param name="right">The rights to check for on each property</param>
		/// <returns>A list of properties on the type matching the access rights</returns>
		private static IEnumerable<PropertyInfo> GetProperties(Type T, DatabaseAccessRights right)
		{
			// Throw an exception if the type is null
			if (T == null)
				throw new ArgumentNullException("T");
			// Initialize the list of return properties
			var returnProperties = new List<PropertyInfo>();
			// Get all of the properties for the type
			var allProperties = T.GetProperties();
			DatabaseAccessAttribute accessAttribute;
			// Loop through all properties
			foreach (var property in allProperties)
			{
				// Property must have a getter and setter
				if (property.GetMethod == null ||
					property.SetMethod == null)
					continue;

				// Get the DataAccessAttribute for the property
				accessAttribute = property.GetCustomAttribute(typeof(DatabaseAccessAttribute)) as DatabaseAccessAttribute;

				// Continue if it doesn't have the access attribute
				if (accessAttribute == null)
					continue;

				// If property has access matching the passed rights, then add to the list
				if ((accessAttribute.Rights & right) == right)
					returnProperties.Add(property);
			}

			// Return the list of properties
			return returnProperties;
		}

		/// <summary>
		/// Logs the passed message to the exception message table in the database
		/// </summary>
		/// <param name="message">The message to be logged in the database</param>
		public static void LogException(string message)
		{
			try
			{
				// build the query
				var query = "INSERT INTO [dbo].[ExceptionMessage] ([Message]) VALUES (@Message)";
				using (var command = new SqlCommand(query, _server._connection))
				{
					// Add the message as a parameter
					command.Parameters.AddWithValue("Message", message);
					// Execute the insert
					var resultCount = command.ExecuteNonQuery();
				}
			}
			catch
			{
				// ignore all exceptions
			}
		}

		/// <summary>
		/// Performs a search for images based on the passed image analysis id and the tolerance
		/// </summary>
		/// <typeparam name="T">The type of object to return</typeparam>
		/// <param name="imageAnalysisId">The analysis id to use as the basis of the search</param>
		/// <param name="tolerance">The tolerance of the search</param>
		/// <returns>A DataServerResults object containing the image results</returns>
		public static DataServerResults<T> SearchByImage<T>(long imageAnalysisId, int tolerance) where T : IEntity
		{
			// Build the predicate for the search procedure's parameters
			var condition = new PredicateGroup(new[] 
				{
					new Predicate("ImageAnalysisId", imageAnalysisId, PredicateOperator.EqualTo),
					new Predicate("Range", tolerance * Color.ColorDistanceVector.MaximumDistanceOnePercent * 0.5, PredicateOperator.EqualTo),
					new Predicate("HueRange", 7.2 * tolerance, PredicateOperator.EqualTo)
				});

			// Execute the search through the SeachByImage stored procedure
			var results = ExecuteReader(typeof(T), "EXEC [dbo].[SearchByImage] @ImageAnalysisId, @Range, @HueRange", condition);

			// Cast the results as the specified type and return them
			return new DataServerResults<T>(condition)
			{
				WasSuccessful = results.WasSuccessful,
				ErrorMessage = results.ErrorMessage,
				Results = results.Results.Count() > 0 ? results.Results.Cast<T>().ToList() : new List<T>()
			};
		}

		/// <summary>
		/// Performs a search for images based on the color distance vector, tolerance, and percent
		/// </summary>
		/// <typeparam name="T">The type of object to return</typeparam>
		/// <param name="vector">The color vector specifying the RGB value</param>
		/// <param name="tolerance">The tolerance for the search</param>
		/// <param name="percent">The percent match for the search</param>
		/// <returns>A DataServerResults object containing the image results</returns>
		public static DataServerResults<T> SearchByColor<T>(SIR.Color.ColorDistanceVector vector, int tolerance, int percent) where T : IEntity
		{
			// Calculate the tolerance as distance
			var colorTolerance = Color.ColorDistanceVector.MaximumDistanceOnePercent * tolerance * 0.5;
			// Build the parameters for the proc
			var condition = new PredicateGroup(new[] 
				{
					new Predicate("Range0L", vector.DistanceFrom[0] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range0H", vector.DistanceFrom[0] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range1L", vector.DistanceFrom[1] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range1H", vector.DistanceFrom[1] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range2L", vector.DistanceFrom[2] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range2H", vector.DistanceFrom[2] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range3L", vector.DistanceFrom[3] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range3H", vector.DistanceFrom[3] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range4L", vector.DistanceFrom[4] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range4H", vector.DistanceFrom[4] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range5L", vector.DistanceFrom[5] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range5H", vector.DistanceFrom[5] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range6L", vector.DistanceFrom[6] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range6H", vector.DistanceFrom[6] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range7L", vector.DistanceFrom[7] - colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Range7H", vector.DistanceFrom[7] + colorTolerance, PredicateOperator.EqualTo),
					new Predicate("Percent", percent, PredicateOperator.EqualTo),
				}, true);

			// Get the results from the SearchByColor stored procedure
			var results = ExecuteReader(typeof(T), "EXEC [dbo].[SearchByColor] " +
				"@Range0L, @Range0H, @Range1L, @Range1H, @Range2L, @Range2H, @Range3L, " +
				"@Range3H, @Range4L, @Range4H, @Range5L, @Range5H, @Range6L, @Range6H, " +
				"@Range7L, @Range7H, @Percent", condition);

			// Cast the results and return them as successful
			return new DataServerResults<T>(condition)
			{
				WasSuccessful = results.WasSuccessful,
				ErrorMessage = results.ErrorMessage,
				Results = results.Results.Count() > 0 ? results.Results.Cast<T>().ToList() : new List<T>()
			};
		}
	}
}
